if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_schedule_update_list')
          and type in ('P','PC'))
   drop procedure bd_proc_schedule_update_list
go


create procedure bd_proc_schedule_update_list (@shift_info varchar(max),@ip varchar(50),@operator varchar(50),@doType int = 0) as
BEGIN

	--  异常扑捉机制
	BEGIN TRY


	-- 开启事务
		BEGIN TRAN


			create table #shift_info(
				id int identity,
				user_no varchar(50) COLLATE Chinese_PRC_CI_AS,
				user_name varchar(50)	COLLATE Chinese_PRC_CI_AS,
				user_serial varchar(50) COLLATE Chinese_PRC_CI_AS,
				dept_name varchar(50)	COLLATE Chinese_PRC_CI_AS,
				dept_serial varchar(50) COLLATE Chinese_PRC_CI_AS,
				column_name varchar(50)	COLLATE Chinese_PRC_CI_AS,
				column_value varchar(200)	COLLATE Chinese_PRC_CI_AS,
				year_month varchar(50) COLLATE Chinese_PRC_CI_AS,
				shift_id varchar(50) COLLATE Chinese_PRC_CI_AS,
				shift_bh varchar(50) COLLATE Chinese_PRC_CI_AS,
				shift_date date
			)
			--明细数据
			create table #shift_info_detail(
				id int identity,
				user_no varchar(50) COLLATE Chinese_PRC_CI_AS,
				user_name varchar(50)	COLLATE Chinese_PRC_CI_AS,
				user_serial varchar(50) COLLATE Chinese_PRC_CI_AS,
				dept_name varchar(50)	COLLATE Chinese_PRC_CI_AS,
				dept_serial varchar(50) COLLATE Chinese_PRC_CI_AS,
				column_name varchar(50)	COLLATE Chinese_PRC_CI_AS,
				column_value varchar(200)	COLLATE Chinese_PRC_CI_AS,
				year_month varchar(50) COLLATE Chinese_PRC_CI_AS,
				shift_id int null,
				shift_bh varchar(50) COLLATE Chinese_PRC_CI_AS,
				shift_date date,
				shift_is_rest int null,
				is_have_ot int null,
				is_created_ot int null
			)

			--创建 返回不满足排班的人的原因
			create table #return_table(
				user_serial VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				user_no VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				user_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				dept_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				error_msg VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
			)

			--kt_paiba数据
			insert into #shift_info(user_serial,column_name,column_value,year_month)
			select userSerial,columnName,columnValue,yearMonth from openjson(@shift_info)
			with (
				userSerial varchar(50),
				columnName varchar(50),
				columnValue varchar(200),
				yearMonth varchar(50)
			);




			-- 更新排班人员信息
			update a set a.user_no = isnull(b.user_no,''), a.user_name = isnull(b.user_lname,''), a.dept_name = isnull(c.dep_name,''), a.dept_serial = isnull(c.dep_serial,'')
			from #shift_info a
			left join dt_user b on a.user_serial = b.user_serial
			left join dt_dep c on b.user_dep = c.dep_serial


			-- 更新日期
			update #shift_info set shift_date = convert(varchar(10),concat(year_month,'-',substring(column_name,2,len(column_name)-1)),120)
			where isnull(column_name,'') <> '' and isnull(year_month,'') <> ''


			-- 更新班次信息
			update #shift_info
			set shift_bh = (SELECT SUBSTRING(a.v,0,len( a.v )) FROM(
				SELECT (SELECT SUBSTRING(s.value,0,charindex( '|', s.value )) + ','
				FROM string_split ( SUBSTRING ( column_value, 4, len(column_value )), ',' ) s
				FOR XML PATH ( '' )) v ) a)
			where isnull(column_value,'') <> '' and (isnull(column_value,'') <> '[X]' or isnull(column_value,'') <> '[F]')


			-- 更新班次ID
			update a set a.shift_id = (
				select SUBSTRING(a.v,0,len( a.v ))  from (
					select (select convert(varchar(10),z.id)+',' from zt_banci z
					where z.bh in( select * from string_split(a.shift_bh,','))
					FOR xml path('')) v) a)
			from #shift_info a

			--校验
			-- 1检测是否为封账状态
			insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
			select distinct a.user_serial,a.user_no,a.user_name,a.dept_name,'该时段考勤存在已经封账,不能重新排班'
			from #shift_info  a
			left join bd_kq_accounts b on a.user_serial = b.user_serial and year(a.shift_date) = b.year and month(a.shift_date) = b.month
			where isnull(b.lock_status,0)>0

			-- 删除已经封账的数据(以用户为单位)
			delete a
			from #shift_info a
			inner join #return_table b on a.user_serial = b.user_serial

			--2检测有无加班单
			--2检测之前是否有排班并且排班有生成的加班单（若该加班单存在，则该人本次全部日期都不能排班）
			insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
			select distinct a.user_serial,a.user_no,a.user_name,a.dept_name,'该时段存在排班加班单，不能重新排班'
			from #shift_info a
			inner join bd_kq_schedule_detail d on a.user_serial = d.user_serial and a.shift_date = d.shift_date and d.is_created_ot = 1

			-- 删除已经有加班单的人员数据
			delete a
			from #shift_info a
			inner join #return_table b on a.user_serial = b.user_serial

			--3检测指定放班，前提是is_have_ot=0 即放班的排班的班次没有附带加班
			insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
			select distinct a.user_serial,a.user_no,a.user_name,a.dept_name,'该时段存在排班班次附带加班，不能放班'
			from #shift_info a
			INNER JOIN zt_shid sd on sd.parent = a.shift_bh and sd.lx=1
			where SUBSTRING(isnull(a.column_value,''),1,3)='[F]'
			-- 删除已经有加班单的人员数据
			delete a
			from #shift_info a
			inner join #return_table b on a.user_serial = b.user_serial



			-- 删除数据详情表
			delete a
			from bd_kq_schedule_detail a
			inner join #shift_info b on a.user_serial = b.user_serial and a.shift_date = b.shift_date;

			--形成明细数据
			 insert into #shift_info_detail(user_no,user_name,user_serial,dept_name,dept_serial,column_name,column_value,
			 year_month,shift_id,shift_bh,shift_date)
				select a.user_no,
							a.user_name,
							a.user_serial,
							a.dept_name,
							a.dept_serial,
							a.column_name,
							a.fh+a.value+'|'+z.mc ,
							a.year_month,
							z.id,
							a.value,
							a.shift_date
				from (
					select s.user_no,s.user_name,s.user_serial,s.dept_name,s.dept_serial,s.column_name,s.year_month,s.shift_date,SUBSTRING(s.column_value,0,4) as fh,r.*

					from #shift_info s
					cross apply (
						select * from string_split(s.shift_bh,',')
					) as r
				) a
				INNER JOIN zt_banci z on a.value = z.bh

				--根据日历，将非工作日的符号改为[X]
				update a
					set a.column_value = case when (d.is_weekend=1 or d.is_statutory=1) then concat('[X]',substring(a.column_value,4,len(a.column_value))) else  a.column_value end,
							a.shift_is_rest = case when (d.is_weekend=1 or d.is_statutory=1) then 1 else  0 end
				from  #shift_info_detail a
				INNER JOIN dt_user u on a.user_serial = u.user_serial
				INNER JOIN bd_kq_work_calendar_detail d on d.parent_id = u.user_work_calendar and a.shift_date = d.work_date

				--处理	is_have_ot,is_created_ot
				--班次有加班时段，则is_have_ot = 1 ，is_created_ot=0
				--班次没有加班字段 则is_have_ot = 0 ，is_created_ot=0
				update s
					set s.is_have_ot = case when isnull(sd.bh,0)=0 then (case when (isnull(bc.id,0)!=0 and s.shift_is_rest=1) then 1 else 0 end) else 1 end ,
							s.is_created_ot = 0
				from #shift_info_detail s
				left join zt_banci bc on bc.id = isnull(s.shift_id,0)
				left JOIN zt_shid sd ON bc.bh = sd.parent and sd.lx=1

			-- 插入数据详情表
			insert into bd_kq_schedule_detail(user_serial,shift_id,shift_name,shift_date,shift_begin,shift_end,shift_sign_begin,shift_sign_end,shift_late,
            shift_early,shift_duty_on_delay,shift_duty_off_delay,shift_workday,shift_workday_hours,is_enjoyment_subsidized,middle_shift_start_time,
            middle_shift_end_time,night_shift_start_time,night_shift_end_time,repast_start_time,repast_end_time,repast_start_time2,repast_end_time2,
            shift_is_rest,is_have_ot,is_created_ot,update_id,update_time)
			select a.user_serial,a.shift_id,a.column_value,a.shift_date,isnull(c.sbsj,0),isnull(c.xbsj,0),isnull(c.sbks,0),isnull(c.xbjs,0),
            isnull(c.cdsj,0),isnull(c.ztsj,0),isnull(c.bjcd,0),isnull(c.bjzt,0),isnull(c.alld,0),isnull(c.allt,0),isnull(c.is_enjoyment_subsidized,0),
            isnull(c.middle_shift_start_time,0),isnull(c.middle_shift_end_time,0),isnull(c.night_shift_start_time,0),isnull(c.night_shift_end_time,0),
            isnull(c.repast_start_time,0),isnull(c.repast_end_time,0),isnull(c.repast_start_time2,0),isnull(c.repast_end_time2,0),a.shift_is_rest,
            a.is_have_ot,a.is_created_ot,@operator,getdate()
			from #shift_info_detail a
			inner join zt_banci b on a.shift_id = b.id
			inner join zt_shid c on b.bh = c.parent
			where c.lx = 0


			declare @sql varchar(max) = '';

			select @sql += concat('update kt_paiba set ',column_name,' = ''',column_value,''', gly_no = ''',@operator,''' where user_serial = ''',user_serial,''' and rq = ''',year_month,'''; ')
			from #shift_info

			-- 执行更新排班表
			exec(@sql);


			--创建一个游标
			declare my_cursor cursor for select user_serial,shift_date from #shift_info

			--变量
			declare @shift_date date;
			declare @user_serial varchar(200);
			--打开游标
			open my_cursor
			fetch next from my_cursor into @user_serial,@shift_date  --获取my_cursor的下一条数据，其中为两个字段分别赋值给@id,@name
			while @@FETCH_STATUS=0 begin --假如检索到了数据继续执行
				--1调用放班操作请假单存储过程
				--参数：@user_serial  ，@shift_date
				EXEC bd_proc_schedule_fb_leave @user_serial,@shift_date,@operator

				-- 2调用考勤计算(排班4)
				if @doType = 0
				BEGIN
					EXEC bd_proc_timecard_extend @shift_date,@shift_date,@user_serial,4
				END
				fetch next from my_cursor into @user_serial,@shift_date --获取下一条数据并赋值给变量

			end--关闭释放游标
			close my_cursor
			deallocate my_cursor

	--  提交事务
		COMMIT TRAN

--  结束异常捕捉
	END TRY

	--  异常处理
			BEGIN CATCH
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			END;
			RAISERROR (@errStr, 16, 1);
		END CATCH


	select * from #return_table

--  清除临时表
  drop table #shift_info
  drop table #return_table
  drop table #shift_info_detail


END
go
